package model.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Observable;
import java.util.Properties;

import model.schedule.Ad;
import model.schedule.AdAuctionHandler;
import model.schedule.AdFixedHandler;
import model.schedule.AdRelaxedHandler;



public class Database extends Observable {
	private final int statusCodeAuctionEnded = 5;

	private Connection connection;

	public Database() {

	}

	public void connect() {
		connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Properties properties = new Properties();
			properties.put("useUnicode", "true");
			properties.put("characterEncoding", "UTF-8");
			connection = DriverManager
					.getConnection("jdbc:mysql://localhost/tradingonrails?"
							+ "user=tradingonrails&password=tradingonrails", properties);
			setChanged();
			notifyObservers("Database connection established");
		} catch (SQLException ex) {
			setChanged();
			notifyObservers(ex);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public ArrayList<Ad> getAds(CalendarWrapper fromTime, CalendarWrapper toTime) {
		ArrayList<Ad> ads = new ArrayList<Ad>();
		try {

			ResultSet rs = getAdsResultSet(fromTime, toTime, "auctions");
			while (rs.next())
				ads.add(new AdAuctionHandler(rs, this));

			rs = getAdsResultSet(fromTime, toTime, "relaxeds");
			while (rs.next())
				ads.add(new AdRelaxedHandler(rs, this));

			rs = getAdsResultSet(fromTime, toTime, "fixed_ads", "fixed_id");
			while (rs.next())
				ads.add(new AdFixedHandler(rs, this));

		} catch (Exception e) {
			e.printStackTrace();
		}
		return ads;
	}

	private ResultSet getAdsResultSet(CalendarWrapper fromTime,
			CalendarWrapper toTime, String subTable) throws SQLException {
		return getAdsResultSet(fromTime, toTime, subTable, "id");
	}

	private ResultSet getAdsResultSet(CalendarWrapper fromTime,
			CalendarWrapper toTime, String subTable, String subTableId)
			throws SQLException {
		return executeQuery("SELECT ads.*, " + subTable + "." + subTableId
				+ " as thisId FROM ads " + " INNER JOIN " + subTable + " ON "
				+ subTable + ".ad_id=ads.id " + " WHERE ads.enddate > '"
				+ fromTime + "' AND ads.enddate < '" + toTime
				+ "' AND ads.ad_status_id != " + statusCodeAuctionEnded);
	}

	public HashMap<String, String> getFieldsFrom(String table, int id) {
		// System.out.println("getFieldsFrom "+table+" "+id);

		HashMap<String, String> fields = new HashMap<String, String>();

		try {
			ResultSet rs = executeQuery("SELECT * FROM " + table
					+ " WHERE id='" + id + "'");
			rs.first();
			ResultSetMetaData metaData = rs.getMetaData();
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				String columnName = metaData.getColumnName(i);
				String columnValue = rs.getString(columnName);
				fields.put(columnName, columnValue);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return fields;
	}

	private ResultSet executeQuery(String string) throws SQLException {
		// System.out.println(string);
 
		Statement stmt = connection.createStatement();
		ResultSet rs;

		rs = stmt.executeQuery(string);
		return rs;
	}

	private void executeUpdate(String string) throws SQLException {
		//System.out.println(string);

		Statement stmt = connection.createStatement();

		stmt.executeUpdate(string);
	}

	public User getHighestBidder(int auction) {
		try {
			ResultSet rs = executeQuery("SELECT user_id, MAX(max) FROM bids WHERE auction_id='"
					+ auction + "'");
			rs.next();
			int user_id = rs.getInt("user_id");
			if (user_id == 0)
				return null;
			return new User(this, user_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public User getSeller(int ad) {
		try {
			ResultSet rs = executeQuery("SELECT user_id FROM ads WHERE id='"
					+ ad + "'");
			rs.next();
			int user_id = rs.getInt("user_id");
			return new User(this, user_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void markAsHandled(int ad) {
		try {
			executeUpdate("UPDATE ads SET ad_status_id='"
					+ this.statusCodeAuctionEnded + "' WHERE id='" + ad + "'");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void markAsWon(int ad, int seller_id, int buyer_id) {
		try {
			markAsHandled(ad);

			executeUpdate("INSERT INTO judgements (ad_id, seller_id, buyer_id) VALUES ('"
					+ ad + "','" + seller_id + "','" + buyer_id + "')");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void queueEmail(String message, String email, String subject) {
		try {
			String created = (new CalendarWrapper()).toString();
			String receiver = email;
			String sender = "noreply@tradingonrails.com";
			executeUpdate("INSERT INTO emails (message, created, receiver, sender, subject) VALUES ('"
					+ message
					+ "','"
					+ created
					+ "','"
					+ receiver
					+ "','"
					+ sender + "','" + subject + "')");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
